﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using FMPCore;

namespace Server
{
    public class ContactListDB
    {
        /// <summary>
        /// The datasource to access the database
        /// </summary>
        private const string m_DATASOURCE = "Data Source = FMP.sqlite";

        /// <summary>
        /// Gets the contact list for a specified user
        /// </summary>
        /// <param name="username">The username</param>
        /// <returns>A lsit of contacts</returns>
        public List<Contact> GetContactList(string username)
        {
            System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} is getting it's contact list.", DateTime.Now, username));
            List<Contact> contractList = new List<Contact>();

            using (SQLiteConnection conn = new SQLiteConnection(m_DATASOURCE))
            {
                try
                {
                    conn.Open();

                    const string sqlGetUsernamePassword = @"SELECT C.contactListID, 
                                                                    C.userName1, 
                                                                    U.loggedIn AS User1Status, 
                                                                    C.userName2, 
                                                                    U2.loggedIn AS User2Status 
                                                            FROM  tblContactList C INNER JOIN tblUsers U 
                                                                ON C.userName1 = U.userName 
                                                            INNER JOIN tblUsers U2 
                                                                ON C.userName2 = U2.userName 
                                                            WHERE C.userName1 = @Username OR C.userName2 = @Username";
                    using (SQLiteCommand cmd = new SQLiteCommand(sqlGetUsernamePassword, conn))
                    {
                        cmd.Parameters.AddWithValue("@Username", username);
                        SQLiteDataReader reader = cmd.ExecuteReader();

                        while(reader.Read())
                        {
                            Contact con = new Contact();

                            if ((con = GetContact(username, reader["userName1"] as string, reader["User1Status"])) != null)
                            {
                                contractList.Add(con);
                            }
                            else if ((con = GetContact(username, reader["userName2"] as string, reader["User2Status"])) != null)
                            {
                                contractList.Add(con);
                            }
                        }

                        reader.Close();
                    }
                }
                catch (SQLiteException sqlE)
                {
                    System.Diagnostics.Trace.TraceError(sqlE.ToString());
                    return new List<Contact>();
                }
            }

            return contractList;
        }

        /// <summary>
        /// Creates a contact from
        /// </summary>
        /// <param name="username">Username of current user</param>
        /// <param name="userNameSQL">Username retrived from the database</param>
        /// <param name="userNameSQLStatus">The status of the user retrived from the database</param>
        /// <returns>A Contact, otherwise null</returns>
        private Contact GetContact(string username, string userNameSQL, object userNameSQLStatus)
        {
            Contact con = null;
            
            if ((!string.IsNullOrEmpty(userNameSQL) && userNameSQL != username) && userNameSQLStatus != null)
            {
                con = new Contact();
                con.UserName = userNameSQL;
                int status = Convert.ToInt32(userNameSQLStatus);
                con.Status = (Contact.UserStatus)status;
            }

            return con;
        }

        /// <summary>
        /// Adds a contact to the current users contact list
        /// </summary>
        /// <param name="currentUser">The logged in user</param>
        /// <param name="userToAdd">The user that the current user wants to add</param>
        /// <returns>True if the user was added otherwise false</returns>
        public bool AddContact(string currentUser, string userToAdd)
        {
            using (SQLiteConnection conn = new SQLiteConnection(m_DATASOURCE))
            {
                try
                {
                    conn.Open();

                    const string sqlGetUsernamePassword = @"INSERT INTO tblContactList (username1, username2, accepted) VALUES (@username1, @username2, 0)";
                    using (SQLiteCommand cmd = new SQLiteCommand(sqlGetUsernamePassword, conn))
                    {
                        cmd.Parameters.AddWithValue("@username1", currentUser);
                        cmd.Parameters.AddWithValue("@username2", userToAdd);

                        if (cmd.ExecuteNonQuery() == 1)
                        {
                            System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} sucessfully added {2} to it's contact list.", DateTime.Now, currentUser, userToAdd));
                            return true;
                        }
                        else
                        {
                            System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} failed to add {2} to it's contact list.", DateTime.Now, currentUser, userToAdd));
                            return false;
                        }
                    }
                }
                catch (SQLiteException sqlE)
                {
                    System.Diagnostics.Trace.TraceError(sqlE.ToString());
                    return false;
                }
            }
        }

        /// <summary>
        /// Removes a contact to the current users contact list
        /// </summary>
        /// <param name="currentUser">The logged in user</param>
        /// <param name="userToRemove">The user that the current user wants to remove</param>
        /// <returns>True if the user was removed otherwise false</returns>
        public bool RemoveContact(string currentUser, string userToRemove)
        {
            using (SQLiteConnection conn = new SQLiteConnection(m_DATASOURCE))
            {
                try
                {
                    conn.Open();
                    const string sqlGetUsernamePassword = @"DELETE FROM tblContactList WHERE username1 = @username1 AND username2 = @username2";
                    using (SQLiteCommand cmd = new SQLiteCommand(sqlGetUsernamePassword, conn))
                    {
                        cmd.Parameters.AddWithValue("@username1", currentUser);
                        cmd.Parameters.AddWithValue("@username2", userToRemove);

                        if (cmd.ExecuteNonQuery() == 1)
                        {
                            System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} sucessfully removed {2} to it's contact list.", DateTime.Now, currentUser, userToRemove));
                            return true;
                        }
                        else
                        {
                            System.Diagnostics.Trace.WriteLine(string.Format("{0} {1} failed remove {2} to it's contact list.", DateTime.Now, currentUser, userToRemove));
                            return false;
                        }
                    }
                }
                catch (SQLiteException sqlE)
                {
                    System.Diagnostics.Trace.TraceError(sqlE.ToString());
                    return false;
                }
            }
        }
    }
}
